package org.csq.service.impl;

import com.alibaba.fastjson2.JSONArray;
import org.csq.controller.dto.PageDTO;
import org.csq.entity.PageResult;
import org.csq.service.MetadataService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.sql.ResultSetMetaData;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class MetadataServiceImpl implements MetadataService {

    private final DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private final DateFormat dateFormat_TZ = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",Locale.CHINA);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Map<String, Object>> getMetadataByTableName(String tableName) {
        //使用jdbcTemplate查询metadata表中table为tableName的数据
        String sql = "SELECT * FROM metadata WHERE `table` = ? ORDER BY sort";
        List<Map<String, Object>> metadataByTableName = jdbcTemplate.queryForList(sql, tableName);
        metadataByTableName.stream().map(metadata -> {
            if (!ObjectUtils.isEmpty(metadata.get("rule"))) {
                metadata.put("rule", JSONArray.parse(metadata.get("rule").toString()));
            }
            if (!ObjectUtils.isEmpty(metadata.get("dict"))) {
                metadata.put("dict", JSONArray.parse(metadata.get("dict").toString()));
            }
            return metadata;
        }).collect(Collectors.toList());
        return metadataByTableName;
    }

    @Override
    public PageResult getDataByTableName(String tableName, Map<String, Object> query, PageDTO page) {
        validateTableName(tableName); // 安全校验

        query = dataTypeConversion(tableName, query);

        // 分页参数
        int pageNum = Math.max(page.getPageNum(), 1);
        int pageSize = Math.min(page.getPageSize(), 100); // 防止过大
        int offset = (pageNum - 1) * pageSize;

        // 动态构建 WHERE 条件和参数
        StringBuilder whereSql = new StringBuilder();
        List<Object> params = new ArrayList<>();

        if (query != null) {
            for (Map.Entry<String, Object> entry : query.entrySet()) {
                String key = entry.getKey();
                Object value = entry.getValue();

                if (value == null) continue;

                if (value.getClass().isArray()) {
                    Object[] list = (Object[]) value;
                    if (list.length == 2 && list[0] != null && list[1] != null) {
                        params.add(list[0]);
                        params.add(list[1]);
                        whereSql.append(" AND ").append(key).append(" BETWEEN ? AND ? ");
                    }
                } else if (value instanceof Number) {
                    params.add(value);
                    whereSql.append(" AND ").append(key).append(" = ? ");
                } else if (value instanceof String && !((String) value).isEmpty()) {
                    params.add("%" + value + "%");
                    whereSql.append(" AND ").append(key).append(" LIKE ? ");
                }
            }
        }

        String whereClause = whereSql.length() > 0 ? " WHERE 1=1 " + whereSql : "";

        // 1. 查询总数
        String countSql = "SELECT COUNT(*) FROM " + tableName + whereClause;
        Long total = jdbcTemplate.queryForObject(countSql, Long.class, params.toArray());

        // 2. 查询分页数据
        String dataSql = "SELECT * FROM " + tableName +
                whereClause + " ORDER BY id DESC LIMIT ? OFFSET ?";
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(dataSql,
                concat(params, pageSize, offset)); // 工具方法拼接参数

        return new PageResult(total, rows);
    }

    private Object[] concat(List<Object> list, Object... extra) {
        Object[] arr = list.toArray();
        Object[] result = Arrays.copyOf(arr, arr.length + extra.length);
        System.arraycopy(extra, 0, result, arr.length, extra.length);
        return result;
    }

    @Override
    public Map<String, Object> saveData(String tableName, Map<String, Object> data) {
        validateTableName(tableName); // 安全校验

        if (data == null || data.isEmpty()) {
            throw new IllegalArgumentException("数据不能为空");
        }
        data = dataTypeConversion(tableName, data);

        Object id = data.get("id");

        if (id != null) {
            // ========== 更新操作 ==========
            return updateRecord(tableName, data, id);
        } else {
            // ========== 新增操作 ==========
            return insertRecord(tableName, data);
        }
    }

    private Map<String, Object> dataTypeConversion(String tableName, Map<String, Object> data) {
        if (ObjectUtils.isEmpty(data)) {
            return data;
        }
        List<Map<String, Object>> metadataByTableName = getMetadataByTableName(tableName);
        metadataByTableName.stream().forEach(metadata -> {
            if ((!"id".equals(metadata.get("field"))) && (!((boolean) metadata.get("is_update")))) {
                data.remove(metadata.get("field"));
            }else {
                if (metadata.get("field_type").equals("date") || metadata.get("field_type").equals("datetime")) {
                    try {
                        if (!ObjectUtils.isEmpty(data.get(metadata.get("field").toString()))) {
                            Object fieldData = data.get(metadata.get("field").toString());
                            if (fieldData instanceof ArrayList && ((ArrayList) fieldData).size() == 2) {
                                fieldData = new Date[]{dateFormat.parse(((ArrayList) fieldData).get(0).toString()), dateFormat.parse(((ArrayList) fieldData).get(1).toString())};
                            }else if (fieldData instanceof String) {
                                fieldData = dateFormat_TZ.parse(fieldData.toString());
                            }
                            data.put(metadata.get("field").toString(), fieldData);
                        }
                    } catch (ParseException e) {
                        throw new RuntimeException(e);
                    }
                }
            }
        });
        return data;
    }

    // 更新记录
    /**
     * 更新指定表中的一条记录，并返回更新后的完整数据
     * 适用于所有主流数据库（MySQL、PostgreSQL、Oracle、SQL Server、SQLite 等）
     */
    private Map<String, Object> updateRecord(String tableName, Map<String, Object> data, Object id) {
        List<String> setters = new ArrayList<>();
        List<Object> params = new ArrayList<>();

        // 过滤字段，跳过 null 和 id
        for (Map.Entry<String, Object> entry : data.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();
            if ("id".equalsIgnoreCase(key)) {
                continue; // 跳过 id 字段
            }
            if (value == null) {
                continue; // 可选：跳过 null 值，避免覆盖为 NULL
            }
            setters.add(key + " = ?");
            params.add(value);
        }

        if (setters.isEmpty()) {
            throw new IllegalArgumentException("没有可更新的有效字段（非 null 且非 id）");
        }

        // 添加 WHERE 条件参数
        params.add(id);

        String updateSql = "UPDATE " + tableName +
                " SET " + String.join(", ", setters) +
                " WHERE id = ?";

        System.out.println("UPDATE SQL: " + updateSql);
        System.out.println("Params: " + params);

        try {
            // 1. 执行更新
            int rowsAffected = jdbcTemplate.update(updateSql, params.toArray());
            if (rowsAffected == 0) {
                throw new RuntimeException("未找到 ID 为 '" + id + "' 的记录，更新失败");
            }

            // 2. 查询更新后的最新数据（跨数据库兼容方式）
            String selectSql = "SELECT * FROM " + tableName + " WHERE id = ?";
            return jdbcTemplate.queryForObject(selectSql, mapRowToMap(), id);

        } catch (EmptyResultDataAccessException e) {
            // 理论上不会走到这里，因为上面已检查 rowsAffected
            throw new RuntimeException("更新成功但未查到记录（ID: " + id + "），可能已被删除", e);
        } catch (Exception e) {
            throw new RuntimeException("更新失败: " + e.getMessage(), e);
        }
    }

    // 新增记录
    /**
     * 插入一条记录到指定表，并返回插入后的完整记录（含自动生成的 ID）
     * 适用于 MySQL、PostgreSQL、Oracle、SQL Server、SQLite 等所有主流数据库
     */
    private Map<String, Object> insertRecord(String tableName, Map<String, Object> data) {
        List<String> fields = new ArrayList<>();
        List<String> placeholders = new ArrayList<>();
        List<Object> params = new ArrayList<>();

        // 1. 自动生成 UUID 作为主键
        String newId = UUID.randomUUID().toString();
        fields.add("id");
        placeholders.add("?");
        params.add(newId);

        // 2. 添加其他字段
        for (Map.Entry<String, Object> entry : data.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();
            if (value != null) { // 避免插入 null 值（可选）
                fields.add(key);
                placeholders.add("?");
                params.add(value);
            }
        }

        // 3. 构建 INSERT SQL
        String insertSql = "INSERT INTO " + tableName +
                " (" + String.join(", ", fields) + ") " +
                "VALUES (" + String.join(", ", placeholders) + ")";

        System.out.println("INSERT SQL: " + insertSql);
        System.out.println("Params: " + params);

        try {
            // 4. 执行插入
            jdbcTemplate.update(insertSql, params.toArray());

            // 5. 查询刚刚插入的记录（确保跨数据库兼容）
            String selectSql = "SELECT * FROM " + tableName + " WHERE id = ?";
            return jdbcTemplate.queryForObject(selectSql, mapRowToMap(), newId);

        } catch (EmptyResultDataAccessException e) {
            throw new RuntimeException("插入失败：未找到插入的记录（id=" + newId + "）", e);
        } catch (Exception e) {
            throw new RuntimeException("插入失败：" + e.getMessage(), e);
        }
    }

    // 将 ResultSet 映射为 Map<String, Object>
    private RowMapper<Map<String, Object>> mapRowToMap() {
        return (rs, rowNum) -> {
            Map<String, Object> row = new LinkedHashMap<>();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = rs.getObject(i);
                row.put(columnName, value);
            }
            return row;
        };
    }

    public Integer deleteByIds(String tableName, List<Object> ids) {
        validateTableName(tableName);

        if (ids == null || ids.isEmpty()) {
            throw new IllegalArgumentException("ID 列表不能为空");
        }

        // 构建 ? 占位符：?, ?, ?
        String placeholders = String.join(",", Collections.nCopies(ids.size(), "?"));

        String sql = "DELETE FROM " + tableName + " WHERE id IN (" + placeholders + ")";

        System.out.println("SQL: " + sql);
        System.out.println("Params: " + ids);

        try {
            int count = jdbcTemplate.update(sql, ids.toArray());
            return count;
        } catch (Exception e) {
            throw new RuntimeException("删除失败：" + e.getMessage(), e);
        }
    }

    // 表名校验（防止 SQL 注入）
    private void validateTableName(String tableName) {
        if (tableName == null || !tableName.matches("^[a-zA-Z_][a-zA-Z0-9_]*$")) {
            throw new IllegalArgumentException("非法的表名");
        }
    }
}
